﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

public class Data
{
    #region khoi tao bien sql
    public SqlConnection con = new SqlConnection("Data Source=" + ".\\SQLEXPRESS" + "; initial catalog=thanhthientruong; User ID=sa;PWD=6443171;");
    public SqlCommand cmd = new SqlCommand();
    public SqlDataAdapter da = new SqlDataAdapter();
    public SqlDataReader dr;
    #endregion

    #region connect and close data
        public Data()
        {
            cmd.Connection = con;
        }
        private void OpenConnect()
        {
                con.Open();
        }
        public void SetDatabaseOffline()
        {
            try
            {
                cmd.CommandText = "ALTER DATABASE humax SET OFFLINE";
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            catch (Exception)
            {
                cmd.Parameters.Clear();
                throw;
            }
        }
        private void CloseConnect()
        {
            cmd.Parameters.Clear();
            cmd.Cancel();
            con.Close();
        }
        public void Destroy() { cmd.Dispose(); con.Dispose(); }
        #endregion

    #region Doc du lieu
      
    public DataTable GetTableMenu_ByMenuTieuDe(string _tieude)
        {
            try
            {
                OpenConnect();
                cmd.CommandText = "select * from dbo.Menu where MenuTieuDe='"+_tieude+"'";
                da.SelectCommand = cmd;
                DataTable table = new DataTable();
                da.Fill(table);
                return table;
            }
            finally
            {
                CloseConnect();
            }

        }
    public ArrayList danhsachBang(string _tableParent)
    {
        try
        {
            OpenConnect();
            ArrayList listbang = new ArrayList();
            cmd.CommandText = "select " + _tableParent + "Name from dbo." + _tableParent;
            DataTable table = new DataTable();
            da.SelectCommand = cmd;
            da.Fill(table);
            for (int i = 0; i < table.Rows.Count; i++)
            {
                listbang.Add(table.Rows[i][_tableParent +"Name"].ToString());
            }
            return listbang;
        }
        finally
        {
            CloseConnect();
        }
    }
    public bool CheckMenuParent(string _tableName)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select * from dbo." + _tableName;
            dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                return true;
            }
            else return false;
        }
        finally
        {
            dr.Close();
            CloseConnect();
        }
    }
    public string GetMucPhapLuatDescription(string _MucPhapLuatID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select MucPhapLuatDescription from dbo.MucLucPhapLuat where MucPhapLuatID=" + _MucPhapLuatID;
            return cmd.ExecuteScalar().ToString();
        }
        finally
        {
            CloseConnect();
        }
    }
    public DataTable GetTable_DoanhNghiep()
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select * from dbo.DoanhNghiep";
            da.SelectCommand = cmd;
            DataTable table = new DataTable();
            da.Fill(table);
            return table;
        }
        finally
        {
            CloseConnect();
        }
    }
    public DataTable GetTable_By_Name_And_Top_Where(string _name, int top, string _where)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select top "+top+" * from dbo." + _name + " " + _where;
            da.SelectCommand = cmd;
            DataTable table = new DataTable();
            da.Fill(table);
            return table;
        }
        finally
        {
            CloseConnect();
        }
    }
    public DataTable GetTable_By_Where(string _name,string _where)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select * from dbo." + _name + " " + _where;
            da.SelectCommand = cmd;
            DataTable table = new DataTable();
            da.Fill(table);
            return table;
        }
        finally
        {
            CloseConnect();
        }
    }
    public DataTable GetTable_By_Name(string _name)
    {
        try
        {
            OpenConnect(); 
            cmd.CommandText = "select * from dbo." + _name;
            da.SelectCommand = cmd;
            DataTable table = new DataTable();
            da.Fill(table);
            return table;
        }
        finally
        {
            CloseConnect();
        }
    }
    public DataTable GetTable_By_Name_And_Top(string _name, int top, string columnSORT)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select top 5 * from dbo." + _name + " order by " + columnSORT+ " desc";
            da.SelectCommand = cmd;
            DataTable table = new DataTable();
            da.Fill(table);
            return table;
        }
        finally
        {
            CloseConnect();
        }
    }
    public string GetMucLucPhapLuatConName(string _tableParentName, string _tableChildName)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select " + _tableParentName + "Description from dbo." + _tableParentName + " where " + _tableParentName + "Name='" + _tableChildName + "'";
            return cmd.ExecuteScalar().ToString();
        }
        finally
        {
            CloseConnect();
        }
    }
    public string GetTable_MucLucPhapLuat_NameDescriptionByName(string _tablename)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select MucPhapLuatDescription from dbo.MucLucPhapLuat where MucPhapLuatName='" + _tablename + "'";
            return cmd.ExecuteScalar().ToString();
        }
        finally
        {
            CloseConnect();
        }
    }
    public string GetTable_MucLucPhapLuat_NameDescription(string _id)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select MucPhapLuatDescription from dbo.MucLucPhapLuat where MucPhapLuatID='" + _id + "'";
            return cmd.ExecuteScalar().ToString();
        }
        finally
        {
            CloseConnect();
        }
    }
    public string GetTable_MucLucPhapLuat_Name(string _name)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select MucPhapLuatName from dbo.MucLucPhapLuat where MucPhapLuatID='" + _name + "'";
            return cmd.ExecuteScalar().ToString();
        }
        finally
        {
            CloseConnect();
        }
    }
    public string GetTable_MucLucPhapLuat_ID_byName(string _name)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select MucPhapLuatID from dbo.MucLucPhapLuat where MucPhapLuatName='" + _name + "'";
            return cmd.ExecuteScalar().ToString();
        }
        finally
        {
            CloseConnect();
        }
    }
    public string GetTable_MucLucPhapLuat_NameByID(string _MucPhapLuatID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select MucPhapLuatName from dbo.MucLucPhapLuat where MucPhapLuatID=" + _MucPhapLuatID + "";
            return cmd.ExecuteScalar().ToString();
        }
        finally
        {
            CloseConnect();
        }
    }
    public string GetTable_MucLucPhapLuatChild_Name(string _DoanhNghiepID, string _tableName)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select " + _tableName + "Name from dbo." + _tableName + " where " + _tableName + "ID=" + _DoanhNghiepID;
            return cmd.ExecuteScalar().ToString();
        }
        finally
        {
            CloseConnect();
        }
    }
    public string GetTable_MucLucPhapLuatID_ByName(string _DoanhNghiepName, string _tableName)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select " + _DoanhNghiepName + "ID from dbo." + _DoanhNghiepName + " where " + _DoanhNghiepName + "Name='" + _tableName + "'";
            return cmd.ExecuteScalar().ToString();
        }
        finally
        {
            CloseConnect();
        }
    }
    #endregion

    #region admin page
    public bool LoginAdmin(String username, String pass)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "select * from Account where UserName=@UserName and UserPass=@UserPass";
            cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = username;
            cmd.Parameters.Add("@UserPass", SqlDbType.NVarChar).Value = pass;
            dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                return true;
            }
            return false;
        }
        finally 
        {
            dr.Close();
            CloseConnect(); 
        }
    }
    public void Add_Account(string _username, string _pass, string _mail)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.Account(UserName,UserPass,UserEmail,UserRole) values(@UserName,@UserPass,@UserEmail,@UserRole)";
            cmd.Parameters.Add("@Username", SqlDbType.NVarChar).Value = _username;
            cmd.Parameters.Add("@UserPass", SqlDbType.NVarChar).Value = _pass;
            cmd.Parameters.Add("@UserEmail", SqlDbType.NVarChar).Value = _mail;
            cmd.Parameters.Add("@UserRole", SqlDbType.Bit).Value = 1;
            cmd.ExecuteNonQuery();
        }
        finally{
            CloseConnect();
        }
    }

    public void Add_Yahoo(string _YahooName, string _YahooAddress)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.Yahoo(YahooName,YahooAddress) values(@YahooName,@YahooAddress)";
            cmd.Parameters.Add("@YahooName", SqlDbType.NVarChar).Value = _YahooName;
            cmd.Parameters.Add("@YahooAddress", SqlDbType.NVarChar).Value = _YahooAddress;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Edit_Yahoo(string _YahooID, string _YahooName, string _YahooAddress)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.Yahoo set YahooName=@YahooName,YahooAddress=@YahooAddress where YahooID=@YahooID";
            cmd.Parameters.Add("@YahooName", SqlDbType.NVarChar).Value = _YahooName;
            cmd.Parameters.Add("@YahooAddress", SqlDbType.NVarChar).Value = _YahooAddress;
            cmd.Parameters.Add("@YahooID", SqlDbType.Int).Value = _YahooID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_Yahoo(string _YahooID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo.Yahoo where YahooID=@YahooID";
            cmd.Parameters.Add("@YahooID", SqlDbType.Int).Value = _YahooID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_Video(string _VideoTieuDe, string _VideoLink)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.Videos(VideoTieuDe,VideoLink) values(@VideoTieuDe,@VideoLink)";
            cmd.Parameters.Add("@VideoTieuDe", SqlDbType.NVarChar).Value = _VideoTieuDe;
            cmd.Parameters.Add("@VideoLink", SqlDbType.NVarChar).Value = _VideoLink;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Edit_Video(string _VideoID, string _VideoTieuDe, string _VideoLink)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.Videos set VideoTieuDe=@VideoTieuDe,VideoLink=@VideoLink where VideoID=@VideoID";
            cmd.Parameters.Add("@VideoTieuDe", SqlDbType.NVarChar).Value = _VideoTieuDe;
            cmd.Parameters.Add("@VideoLink", SqlDbType.NVarChar).Value = _VideoLink;
            cmd.Parameters.Add("@VideoID", SqlDbType.Int).Value = _VideoID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_Video(string _VideoID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo.Videos where VideoID=@VideoID";
            cmd.Parameters.Add("@VideoID", SqlDbType.Int).Value = _VideoID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Edit_ThongTinCoDinh(string _noidung, string _ThongTinCoDinhName)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.ThongTinCoDinh set TTCoDinhNoiDung=@TTCoDinhNoiDung where TTCoDinhName='"+_ThongTinCoDinhName+"'";
            cmd.Parameters.Add("@TTCoDinhNoiDung", SqlDbType.NVarChar).Value = _noidung;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Edit_ThongTinCoDinh_Image(string _noidung, string _ThongTinCoDinhName, string _image)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.ThongTinCoDinh set TTCoDinhNoiDung=@TTCoDinhNoiDung,TTCoDinhImage=@TTCoDinhImage where TTCoDinhName='" + _ThongTinCoDinhName + "'";
            cmd.Parameters.Add("@TTCoDinhNoiDung", SqlDbType.NVarChar).Value = _noidung;
            cmd.Parameters.Add("@TTCoDinhImage", SqlDbType.NVarChar).Value = _image;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_BanTinPL(string _BanTinPhapLuatNgayDang, string _BanTinPhapLuatNoiDung, string _BanTinPhapLuatTieuDe, string _BanTinPhapLuatNoiDungTomTat, string _BanTinPhapLuatImages)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.BanTinPhapLuat(BanTinPhapLuatNgayDang,BanTinPhapLuatNoiDung,BanTinPhapLuatTieuDe,BanTinPhapLuatNoiDungTomTat,BanTinPhapLuatImages)"
                                + " VALUES(@BanTinPhapLuatNgayDang,@BanTinPhapLuatNoiDung,@BanTinPhapLuatTieuDe,@BanTinPhapLuatNoiDungTomTat,@BanTinPhapLuatImages)";
            cmd.Parameters.Add("@BanTinPhapLuatNgayDang", SqlDbType.DateTime).Value = _BanTinPhapLuatNgayDang;
            cmd.Parameters.Add("@BanTinPhapLuatNoiDung", SqlDbType.NVarChar).Value = _BanTinPhapLuatNoiDung;
            cmd.Parameters.Add("@BanTinPhapLuatTieuDe", SqlDbType.NVarChar).Value = _BanTinPhapLuatTieuDe;
            cmd.Parameters.Add("@BanTinPhapLuatNoiDungTomTat", SqlDbType.NVarChar).Value = _BanTinPhapLuatNoiDungTomTat;
            cmd.Parameters.Add("@BanTinPhapLuatImages", SqlDbType.NVarChar).Value = _BanTinPhapLuatImages;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_BanTinPL(string _BanTinPhapLuatID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo.BanTinPhapLuat where BanTinPhapLuatID=@BanTinPhapLuatID";
            cmd.Parameters.Add("@BanTinPhapLuatID",SqlDbType.Int).Value=_BanTinPhapLuatID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_BieuMauCate(string _CategoryName)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.BieuMauCate(CategoryName) values(@CategoryName)";
            cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = _CategoryName;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_BieuMauCate(string _CategoryID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo.BieuMauCate where CategoryID=@CategoryID";
            cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = _CategoryID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_BieuMauDetail(string _CategoryID,string _CategoryName, string _BieuMauNoiDung)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.BieuMau(CategoryID,BieuMauName,BieuMauNoiDung) values(@CategoryID,@BieuMauName,@BieuMauNoiDung)";
            cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = _CategoryID;
            cmd.Parameters.Add("@BieuMauName", SqlDbType.NVarChar).Value = _CategoryName;
            cmd.Parameters.Add("@BieuMauNoiDung", SqlDbType.NVarChar).Value = _BieuMauNoiDung;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_BieuMauDetail(string _BieuMauID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo.BieuMau where BieuMauID=@BieuMauID";
            cmd.Parameters.Add("@BieuMauID", SqlDbType.Int).Value = _BieuMauID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_NgheLuat(string _CategoryID, string _NgheLuatName, string _NgheLuatNoiDung, string _NgheLuatNoiDungTomTat, string _NgheLuatNgayDang, string _NgheLuatImage)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.NgheLuat(CategoryID,NgheLuatName,NgheLuatNoiDung,NgheLuatNoiDungTomTat,NgheLuatNgayDang,NgheLuatImage)"
                                + " VALUES(@CategoryID,@NgheLuatName,@NgheLuatNoiDung,@NgheLuatNoiDungTomTat,@NgheLuatNgayDang,@NgheLuatImage)";
            cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = _CategoryID;
            cmd.Parameters.Add("@NgheLuatName", SqlDbType.NVarChar).Value = _NgheLuatName;
            cmd.Parameters.Add("@NgheLuatNoiDung", SqlDbType.NVarChar).Value = _NgheLuatNoiDung;
            cmd.Parameters.Add("@NgheLuatNoiDungTomTat", SqlDbType.NVarChar).Value = _NgheLuatNoiDungTomTat;
            cmd.Parameters.Add("@NgheLuatNgayDang", SqlDbType.DateTime).Value = _NgheLuatNgayDang;
            cmd.Parameters.Add("@NgheLuatImage", SqlDbType.NVarChar).Value = _NgheLuatImage;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_NgheLuat(string _NgheLuatID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo.NgheLuat where NgheLuatID=@NgheLuatID";
            cmd.Parameters.Add("@NgheLuatID", SqlDbType.Int).Value = _NgheLuatID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_NhanSu(string _CategoryID, string _NhanSuName, string _NhanSuNoiDung, string _NhanSuNoiDungTomTat, string _NhanSuNgayDang, string _NhanSuImage)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.NhanSu(CategoryID,NhanSuName,NhanSuNoiDung,NhanSuNoiDungTomTat,NhanSuNgayDang,NhanSuImage)"
                                + " VALUES(@CategoryID,@NhanSuName,@NhanSuNoiDung,@NhanSuNoiDungTomTat,@NhanSuNgayDang,@NhanSuImage)";
            cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = _CategoryID;
            cmd.Parameters.Add("@NhanSuName", SqlDbType.NVarChar).Value = _NhanSuName;
            cmd.Parameters.Add("@NhanSuNoiDung", SqlDbType.NVarChar).Value = _NhanSuNoiDung;
            cmd.Parameters.Add("@NhanSuNoiDungTomTat", SqlDbType.NVarChar).Value = _NhanSuNoiDungTomTat;
            cmd.Parameters.Add("@NhanSuNgayDang", SqlDbType.DateTime).Value = _NhanSuNgayDang;
            cmd.Parameters.Add("@NhanSuImage", SqlDbType.NVarChar).Value = _NhanSuImage;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_NhanSu(string _NhanSuID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo.NhanSu where NhanSuID=@NhanSuID";
            cmd.Parameters.Add("@NhanSuID", SqlDbType.Int).Value = _NhanSuID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_DoiTacDoiTac(string _DoiTacNgayDang, string _DoiTacNoiDung, string _DoiTacName, string _DoiTacNoiDungTomTat, string _DoiTacImages)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.DoiTac(CategoryID,DoiTacNgayDang,DoiTacNoiDung,Name,DoiTacNoiDungTomTat,DoiTacImage)"
                                + " VALUES(@CategoryID,@DoiTacNgayDang,@DoiTacNoiDung,@Name,@DoiTacNoiDungTomTat,@DoiTacImage)";
            cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 2;
            cmd.Parameters.Add("@DoiTacNgayDang", SqlDbType.DateTime).Value = _DoiTacNgayDang;
            cmd.Parameters.Add("@DoiTacNoiDung", SqlDbType.NVarChar).Value = _DoiTacNoiDung;
            cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = _DoiTacName;
            cmd.Parameters.Add("@DoiTacNoiDungTomTat", SqlDbType.NVarChar).Value = _DoiTacNoiDungTomTat;
            cmd.Parameters.Add("@DoiTacImage", SqlDbType.NVarChar).Value = _DoiTacImages;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_DoiTac(string _DoiTacID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo.DoiTac where DoiTacID=@DoiTacID";
            cmd.Parameters.Add("@DoiTacID", SqlDbType.Int).Value = _DoiTacID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Update_DoiTacGioiThieu(string _DoiTacNoiDung)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.DoiTac set DoiTacNoiDung=@DoiTacNoiDung where DoiTacID=1";
            cmd.Parameters.Add("@DoiTacNoiDung", SqlDbType.Int).Value = _DoiTacNoiDung;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_YKienKH(string _YKienKhachHangHoTen, string _YKienKhachHangNgayDang, string _YKienKhachHangEmail, string _YKienKhachHangTieuDe, string _YKienKhachHangNoiDungTomTat, string _YKienKhachHangNoiDung)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo.YKienKhachHang(YKienKhachHangHoTen,YKienKhachHangNgayDang,YKienKhachHangEmail,YKienKhachHangTieuDe,YKienKhachHangNoiDungTomTat,YKienKhachHangNoiDung)"
                                + " VALUES(@YKienKhachHangHoTen,@YKienKhachHangNgayDang,@YKienKhachHangEmail,@YKienKhachHangTieuDe,@YKienKhachHangNoiDungTomTat,@YKienKhachHangNoiDung)";
            cmd.Parameters.Add("@YKienKhachHangHoTen", SqlDbType.NVarChar).Value = _YKienKhachHangHoTen;
            cmd.Parameters.Add("@YKienKhachHangNgayDang", SqlDbType.DateTime).Value = _YKienKhachHangNgayDang;
            cmd.Parameters.Add("@YKienKhachHangEmail", SqlDbType.NVarChar).Value = _YKienKhachHangEmail;
            cmd.Parameters.Add("@YKienKhachHangTieuDe", SqlDbType.NVarChar).Value = _YKienKhachHangTieuDe;
            cmd.Parameters.Add("@YKienKhachHangNoiDungTomTat", SqlDbType.NVarChar).Value = _YKienKhachHangNoiDungTomTat;
            cmd.Parameters.Add("@YKienKhachHangNoiDung", SqlDbType.NVarChar).Value = _YKienKhachHangNoiDung;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Edit_YKienKH(string _YKienKhachHangID,string _YKienKhachHangHoTen, string _YKienKhachHangNgayDang, string _YKienKhachHangEmail, string _YKienKhachHangTieuDe, string _YKienKhachHangNoiDungTomTat, string _YKienKhachHangNoiDung)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.YKienKhachHang set YKienKhachHangHoTen=@YKienKhachHangHoTen,YKienKhachHangNgayDang=@YKienKhachHangNgayDang,YKienKhachHangEmail=@YKienKhachHangEmail,YKienKhachHangTieuDe=@YKienKhachHangTieuDe,YKienKhachHangNoiDungTomTat=@YKienKhachHangNoiDungTomTat,YKienKhachHangNoiDung=@YKienKhachHangNoiDung where YKienKhachHangID=@YKienKhachHangID";
            cmd.Parameters.Add("@YKienKhachHangID", SqlDbType.Int).Value = _YKienKhachHangID;
            cmd.Parameters.Add("@YKienKhachHangHoTen", SqlDbType.NVarChar).Value = _YKienKhachHangHoTen;
            cmd.Parameters.Add("@YKienKhachHangNgayDang", SqlDbType.DateTime).Value = _YKienKhachHangNgayDang;
            cmd.Parameters.Add("@YKienKhachHangEmail", SqlDbType.NVarChar).Value = _YKienKhachHangEmail;
            cmd.Parameters.Add("@YKienKhachHangTieuDe", SqlDbType.NVarChar).Value = _YKienKhachHangTieuDe;
            cmd.Parameters.Add("@YKienKhachHangNoiDungTomTat", SqlDbType.NVarChar).Value = _YKienKhachHangNoiDungTomTat;
            cmd.Parameters.Add("@YKienKhachHangNoiDung", SqlDbType.NVarChar).Value = _YKienKhachHangNoiDung;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_YKienKH(string _YKienKhachHangID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo.YKienKhachHang where YKienKhachHangID=@YKienKhachHangID";
            cmd.Parameters.Add("@YKienKhachHangID", SqlDbType.Int).Value = _YKienKhachHangID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Edit_LinkAnhChay(string _url, string _id)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.AnhChay set AnhChayLink=@AnhChayLink where AnhChayID=@AnhChayID";
            cmd.Parameters.Add("@AnhChayID", SqlDbType.NVarChar).Value = _id;
            cmd.Parameters.Add("@AnhChayLink", SqlDbType.NVarChar).Value = _url;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Edit_LinkAnhurl(string _image, string _id)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.AnhChay set AnhChayImage=@AnhChayImage where AnhChayID=@AnhChayID";
            cmd.Parameters.Add("@AnhChayID", SqlDbType.NVarChar).Value = _id;
            cmd.Parameters.Add("@AnhChayImage", SqlDbType.NVarChar).Value = _image;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Edit_LinkSlide5anh(string _url, string _id)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.Slide5anh set AnhChayLink=@AnhChayLink where AnhChayID=@AnhChayID";
            cmd.Parameters.Add("@AnhChayID", SqlDbType.NVarChar).Value = _id;
            cmd.Parameters.Add("@AnhChayLink", SqlDbType.NVarChar).Value = _url;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Edit_LinkSlide5anhUrl(string _image, string _id, string _AnhChayTieuDe)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "update dbo.Slide5anh set AnhChayImage=@AnhChayImage,AnhChayTieuDe=@AnhChayTieuDe where AnhChayID=@AnhChayID";
            cmd.Parameters.Add("@AnhChayID", SqlDbType.Int).Value = _id;
            cmd.Parameters.Add("@AnhChayImage", SqlDbType.NVarChar).Value = _image;
            cmd.Parameters.Add("@AnhChayTieuDe", SqlDbType.NVarChar).Value = _AnhChayTieuDe;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_MucPhapLuatCon(string _Name, string _Description, string _tableParentName, string _tableParentID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "insert into dbo." + _tableParentName + "(MucPhapLuatID," + _tableParentName + "Name," + _tableParentName + "Description) values(@MucPhapLuatID,@" + _tableParentName + "Name,@" + _tableParentName + "Description)";
            cmd.Parameters.Add("@MucPhapLuatID", SqlDbType.NVarChar).Value = _tableParentID;
            cmd.Parameters.Add("@" + _tableParentName + "Name", SqlDbType.NVarChar).Value = _tableParentName + "_" + _Name;
            cmd.Parameters.Add("@" + _tableParentName + "Description", SqlDbType.NVarChar).Value = _Description;
            cmd.ExecuteNonQuery();
            CreatTable_MucPhapLuatCon(_tableParentName + "_" + _Name, _tableParentName);
        }
        finally
        {
            CloseConnect();
        }
    }
    private void CreatTable_MucPhapLuatCon(string _tableName, string _tableColumn)
    {
        cmd.CommandText = "CREATE TABLE [dbo].[" + _tableName + "](" + Environment.NewLine +
                                        "[MucPhapLuatID] [int] NOT NULL," + Environment.NewLine +
                                        "[" + _tableColumn + "ID] [int] NOT NULL," + Environment.NewLine +
                                        "[MucPhapLuat_ConID] [int] IDENTITY(1,1) NOT NULL," + Environment.NewLine +
                                        "[MucPhapLuat_ConTieuDe] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL," + Environment.NewLine +
                                        "[MucPhapLuat_ConNoiDungTomTat] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL," + Environment.NewLine +
                                        "[MucPhapLuat_ConNoiDung] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL," + Environment.NewLine +
                                        "[MucPhapLuat_ConNgayDang] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL," + Environment.NewLine +
                                        "[MucPhapLuat_ConHinhAnh] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL," + Environment.NewLine +
                                     "CONSTRAINT [PK_" + _tableName + "] PRIMARY KEY CLUSTERED " + Environment.NewLine +
                                    "(" + Environment.NewLine +
                                        "[MucPhapLuat_ConID] ASC" + Environment.NewLine +
                                    ")WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]" + Environment.NewLine +
                                    ") ON [PRIMARY]";
            cmd.ExecuteNonQuery();
    }
    public void Delete_MucPhapLuatCon(string _tableParentName, string _tableParentID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "delete from dbo." + _tableParentName + " where " + _tableParentName + "ID=@"+_tableParentName+"ID";
            cmd.Parameters.Add("@" + _tableParentName + "ID", SqlDbType.Int).Value = _tableParentID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_Table(string _tableName)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "drop table " + _tableName;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_MucPhapLuatConChiTiet(string _tableNameID,string _tableName, string _MucPhapLuatID, string _DoanhNghiepID, string MucPhapLuat_ConTieuDe, string _MucPhapLuat_ConNoiDung, string _MucPhapLuat_ConNgayDang, string _MucPhapLuat_ConHinhAnh)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "INSERT INTO dbo." + _tableName + "(MucPhapLuatID," + _tableNameID + ",MucPhapLuat_ConTieuDe,MucPhapLuat_ConNoiDung,MucPhapLuat_ConNgayDang,MucPhapLuat_ConHinhAnh)" +
                                " VALUES(@MucPhapLuatID,@" + _tableNameID + ",@MucPhapLuat_ConTieuDe,@MucPhapLuat_ConNoiDung,@MucPhapLuat_ConNgayDang,@MucPhapLuat_ConHinhAnh)";
            cmd.Parameters.Add("@MucPhapLuatID", SqlDbType.Int).Value = _MucPhapLuatID;
            cmd.Parameters.Add("@" + _tableNameID, SqlDbType.Int).Value = _DoanhNghiepID;
            cmd.Parameters.Add("@MucPhapLuat_ConTieuDe", SqlDbType.NVarChar).Value = MucPhapLuat_ConTieuDe;
            cmd.Parameters.Add("@MucPhapLuat_ConNoiDung", SqlDbType.NVarChar).Value = _MucPhapLuat_ConNoiDung;
            cmd.Parameters.Add("@MucPhapLuat_ConNgayDang", SqlDbType.DateTime).Value = _MucPhapLuat_ConNgayDang;
            cmd.Parameters.Add("@MucPhapLuat_ConHinhAnh", SqlDbType.NVarChar).Value = _MucPhapLuat_ConHinhAnh;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_MucPhapLuatConChiTiet(string _tableName, string _MucPhapLuat_ConID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "DELETE FROM dbo." + _tableName + " where MucPhapLuat_ConID=@MucPhapLuat_ConID";
            cmd.Parameters.Add("@MucPhapLuat_ConID", SqlDbType.Int).Value = _MucPhapLuat_ConID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }

    public void Add_HoiDap(string _MucPhapLuatID, string _HoiDapHoTen, string _HoiDapNoiDungCauHoi, string _HoiDapNoiDungTraLoi, string _HoiDapNgay)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "INSERT INTO dbo.HoiDap(MucPhapLuatID,HoiDapHoTen,HoiDapNoiDungCauHoi,HoiDapNoiDungTraLoi,HoiDapNgay) VALUES(@MucPhapLuatID,@HoiDapHoTen,@HoiDapNoiDungCauHoi,@HoiDapNoiDungTraLoi,@HoiDapNgay)";
            cmd.Parameters.Add("@MucPhapLuatID", SqlDbType.Int).Value = _MucPhapLuatID;
            cmd.Parameters.Add("@HoiDapHoTen", SqlDbType.NVarChar).Value = _HoiDapHoTen;
            cmd.Parameters.Add("@HoiDapNoiDungCauHoi", SqlDbType.NVarChar).Value = _HoiDapNoiDungCauHoi;
            cmd.Parameters.Add("@HoiDapNoiDungTraLoi", SqlDbType.NVarChar).Value = _HoiDapNoiDungTraLoi;
            cmd.Parameters.Add("@HoiDapNgay", SqlDbType.DateTime).Value = _HoiDapNgay;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    public void Delete_HoiDap(string  _HoiDapID)
    {
        try
        {
            OpenConnect();
            cmd.CommandText = "DELETE FROM dbo.HoiDap where HoiDapID=@HoiDapID";
            cmd.Parameters.Add("@HoiDapID", SqlDbType.Int).Value = _HoiDapID;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            CloseConnect();
        }
    }
    #endregion
}
